﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using WebApplication2.Model;
using WebApplication2.Util;

namespace WebApplication2.Server
{
     /// <summary>
    /// projectWebService 的摘要说明
    /// 用于与project有关的连接数据库服务
    /// 
    /// </summary>
    public class ProjectWebService
    {
        public static ProjectWebService Instance;
        static ProjectWebService() 
        {
            //静态实例
            Instance = new ProjectWebService(); 
        }

        /// <summary>
        /// 查询用户信息的List
        /// 主要用于查询各个状态的项目集合
        /// </summary>
        /// <param name="qurryType"></param>
        /// 0：查询全部
        /// 1：查询在策划中的项目
        /// 2：查询在设计中的项目
        /// 3：查询在加工中的项目
        /// 4：查询在调试中的项目
        /// 5：查询已经完成的项目
        /// <returns></returns>
        public List< ProjectInfo> GetProjectList(int queryType) 
        {
            string sql="";
            switch (queryType) 
            {
                case 0:
                    sql = string.Format(@"select * from T_ProjectInfo");
                    break;
                case 1:
                    sql = string.Format(@"select * from T_ProjectInfo where F_Status=1");
                    break;
                case 2:
                    sql = string.Format(@"select * from T_ProjectInfo where F_Status=2");
                    break;
                case 3:
                    sql = string.Format(@"select * from T_ProjectInfo where F_Status=3");
                    break;
                case 4:
                    sql = string.Format(@"select * from T_ProjectInfo where F_Status=4");
                    break;
                case 5:
                    sql = string.Format(@"select * from T_ProjectInfo where F_Status=5");
                    break;
                case 6:
                    sql = string.Format(@"select * from T_ProjectInfo where F_Status=6");
                    break;
            }

            DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
            if (rows != null && rows.Count > 0)
                return getProjectInfo(rows);
            return null;
        }

      

        public ProjectInfo IsProjectExist(string id)
        {
            string strWhere = string.Format(@" WHERE F_ID='"+id+"'", id.Replace("'", "''"));
            List<ProjectInfo> prolist = getList(strWhere);
            return prolist != null ? prolist[0] : null;
        }


        /// <summary>
        /// 添加新项目服务
        /// 将projectInfo的数据插入到数据库
        /// </summary>
        /// <returns></returns>
        public int AddNewProject(ProjectInfo projectInfo)
        {
            string sql = "";
          

            sql = string.Format(@"INSERT INTO T_ProjectInfo(F_ID,F_NewID, F_Name, F_ChargeMan, F_UserArea, F_Input, F_Finishe, F_Budge,F_RealBudge,F_Value,F_MCharge,F_MDesign,F_ECharge,F_EDesign,F_Status,F_Cap,F_ProjectType) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')",
                 projectInfo.Id, projectInfo.NewID, projectInfo.Name, projectInfo.ChargeMan, projectInfo.UserArea, projectInfo.InputTime, projectInfo.FinishTime, projectInfo.Budget, projectInfo.RealBudge, projectInfo.Value, projectInfo.MCharge, projectInfo.MDesign, projectInfo.Echarge, projectInfo.EDesign, projectInfo.Status,projectInfo.Caps,projectInfo.ProType);

            //sql = string.Format(@"INSERT INTO T_ProjectInfo(F_ID, F_Name, F_ChargeMan, F_UserArea, F_Input, F_Finishe, F_Budge,F_RealBudge,F_Value,F_MCharge,M_Design,F_ECharge,EDesign,F_Status) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')",
            //  projectInfo.Id, projectInfo.Name, projectInfo.ChargeMan, projectInfo.UserArea, projectInfo.InputTime, projectInfo.FinishTime, projectInfo.Budget, projectInfo.RealBudge, projectInfo.Value, projectInfo.MCharge, projectInfo.MDesign, projectInfo.Echarge, projectInfo.EDesign, projectInfo.Status);

            Console.WriteLine(sql);

            int res = 0;
            res = 1;
            res = 0;
            res = MrDBAccess.ExecuteNonQuery(sql);
            return res;
        }

        /// <summary>
        /// 通过项目名搜索的服务
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<ProjectInfo> SearchServiceByName(string name)
        {
            string sql = "";
            sql = @"select * from T_ProjectInfo WHERE F_Name LIKE '%" + name + "%'";
            DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
            if (rows != null && rows.Count > 0)
                return getProjectInfo(rows);
            return null;
        }

        public List<ProjectInfo> SearchServiceByChargeMan(string chargeMan)
        {
            string sql = "";
            sql = @"select * from T_ProjectInfo WHERE F_ChargeMan LIKE '%" + chargeMan + "%'";
            DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
            if (rows != null && rows.Count > 0)
                return getProjectInfo(rows);
            return null;

        }

        public int DeletePro(string name, string id)
        {
            int res = 0;
            string sql = "";
            //name为空，则使用id作为标识，反之亦然
            if (name == null)
            {
                sql = string.Format(@"DELETE FROM T_ProjectInfo WHERE F_ID ='{0}'", id);
            }
            else
            {
                sql = string.Format(@"DELETE FROM T_ProjectInfo WHERE F_Name ='{0}'", name);
            }
            res = MrDBAccess.ExecuteNonQuery(sql);
            return res;
        }

        public int UpdateStatus(string setstatus,string id)
        {
            int status = Convert.ToInt16(setstatus);
            int res = 0;
            string sql = (@"update T_ProjectInfo set F_Status="+status+" where F_ID='"+id+"'");

            res = MrDBAccess.ExecuteNonQuery(sql);
  
            return res;
        }




        /**********************************************************/

        // 基础方法

        /**********************************************************/
        private List<ProjectInfo> getList(string strWhere)
        {
            string sql = string.Format(@"SELECT * FROM T_ProjectInfo {0}", strWhere);
            DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
            return getProjectInfo(rows);
        }

        private List<ProjectInfo> getProjectInfo(DataRowCollection rows)
        {
            if (rows != null && rows.Count > 0)
            {
                List<ProjectInfo> list = new List<ProjectInfo>();
                int iRowLength = rows.Count;
                for (int i = 0; i < iRowLength; i++)
                {
                    ProjectInfo projectInfo = new ProjectInfo();
                    projectInfo.Id = DataBase.ObjectToString(rows[i]["F_ID"]);
                    projectInfo.Name = DataBase.ObjectToString(rows[i]["F_Name"]);
                    projectInfo.ChargeMan = DataBase.ObjectToString(rows[i]["F_ChargeMan"]);
                    projectInfo.UserArea = DataBase.ObjectToString(rows[i]["F_UserArea"]);
                    projectInfo.Budget = DataBase.ObjectToInt(rows[i]["F_Budge"]);
                    projectInfo.RealBudge = DataBase.ObjectToInt(rows[i]["F_RealBudge"]);
                    projectInfo.Value = DataBase.ObjectToInt(rows[i]["F_Value"]);
                    projectInfo.InputTime = DateTime.Parse(DataBase.ObjectToString(rows[i]["F_Input"]));
                    projectInfo.FinishTime = DateTime.Parse(DataBase.ObjectToString(rows[i]["F_Finishe"]));
                    projectInfo.MCharge = DataBase.ObjectToString(rows[i]["F_MCharge"]);
                    projectInfo.MDesign = DataBase.ObjectToString(rows[i]["F_MDesign"]);
                    projectInfo.Echarge = DataBase.ObjectToString(rows[i]["F_ECharge"]);
                    projectInfo.EDesign = DataBase.ObjectToString(rows[i]["F_EDesign"]);
                    projectInfo.Status = DataBase.ObjectToInt(rows[i]["F_Status"]);
                    projectInfo.Caps = DataBase.ObjectToInt(rows[i]["F_Cap"]);
                    projectInfo.ProType = DataBase.ObjectToInt(rows[i]["F_ProjectType"]);
                    ////用项目ID去查询PR单的钱
                    projectInfo.LessBudget = projectInfo.Budget - less(projectInfo.Id);
                    projectInfo.NewID = DataBase.ObjectToString(rows[i]["F_NewID"]);
                    list.Add(projectInfo);
                }
                return list;
            }
            return null;
        }

        //计算项目已经使用的预算
        public int less(string  pro_id) {
         
            string sql = "select * from T_Budget  where ProJect_ID='"+pro_id+"'";
            DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
            int iRowLength = rows.Count;
            int total=0;
            for (int i = 0; i < iRowLength; i++) {
                total = DataBase.ObjectToInt(rows[i]["PR_Budget"])+total;
            }

          return total; 
        }

     
    }
}